package com.shuzhi.stationarysource.modules.job.repository;


import com.shuzhi.stationarysource.modules.job.domain.WryBaseInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;


/**
 * sunfeng
 */
public interface WryBaseInfoRepository extends JpaRepository<WryBaseInfo, Long> {

    /**
     *  根据isBsts查询是否为省部推送数据
     * @param isBsts
     * @return
     */
    List<WryBaseInfo> findAllByIsbsts(String isBsts);





    /**
     * 封装查询未推送的数据
     * @param pageable
     * @return
     */
    @Query(value = "SELECT ENTERID,ENTERNAME,ENTERADDRESS,TYSHXYDM,REGIONNAME,CODE_TRADE,TRADENAME,LONGITUDE,LATITUDE,STATUS,DATASOURCE,CODE_REGION FROM (\n" +
            "SELECT b.P_DISTRICT AS CODE_REGION, a.ID AS ENTERID,a.COMPANY_NAME AS ENTERNAME,b.OPER_ADDRESS AS ENTERADDRESS,a.CREDIT_CD AS TYSHXYDM,r.region_name AS REGIONNAME,b.HYID" +
            " AS CODE_TRADE,b.HYNAME AS TRADENAME,CASE WHEN b.OPER_STATUS=1 THEN 0 WHEN b.OPER_STATUS=2 THEN 1 WHEN b.OPER_STATUS=3 THEN 2 " +
            "WHEN b.OPER_STATUS=4 THEN 3 ELSE 0 END AS STATUS,'00' AS DATASOURCE,b.LONGITUDE AS LONGITUDE,b.LATITUDE " +
            "AS LATITUDE FROM WRY_KEY_INFO a INNER JOIN WRY_BASE_INFO b ON a.id=b.wry_key_id INNER JOIN WRY_ENV_BASE_INFO c " +
            "ON a.id=c.wry_key_id LEFT JOIN REGION_CODE r ON b.P_DISTRICT=r.region_code WHERE (b.isbsts='' OR b.isbsts IS NULL) " +
            "AND len (b.P_DISTRICT)=6 AND len (b.oper_ADDRESS)> 1 AND len (a.CREDIT_CD)=18) t WHERE REGIONNAME IS NOT NULL AND " +
            "REGIONNAME !=''",nativeQuery = true)
    List<Map<String, Object>> findAllNoPush(Pageable pageable);

    /**
     * 更改是否为固定点源的状态
     * @param wryKeyId
     * @return
     */
    @Modifying
    @Query(value = "UPDATE WRY_KEY_INFO SET isbsts='2' where WRY_KEY_ID=?1",nativeQuery = true)
    Integer modifyingIsbstsStatus(Long wryKeyId);

    @Query(value = "SELECT ENTERID,ENTERNAME,ENTERADDRESS,TYSHXYDM,REGIONNAME,CODE_TRADE,TRADENAME,LONGITUDE,LATITUDE,STATUS,DATASOURCE, CODE_REGION FROM (\n" +
            "SELECT b.P_DISTRICT AS CODE_REGION, a.ID AS ENTERID,a.COMPANY_NAME AS ENTERNAME,b.OPER_ADDRESS AS ENTERADDRESS,a.CREDIT_CD AS TYSHXYDM,r.region_name AS REGIONNAME,b.HYID" +
            " AS CODE_TRADE,b.HYNAME AS TRADENAME,CASE WHEN b.OPER_STATUS=1 THEN 0 WHEN b.OPER_STATUS=2 THEN 1 WHEN b.OPER_STATUS=3 THEN 2 " +
            "WHEN b.OPER_STATUS=4 THEN 3 ELSE 0 END AS STATUS,'00' AS DATASOURCE,b.LONGITUDE AS LONGITUDE,b.LATITUDE " +
            "AS LATITUDE FROM WRY_KEY_INFO a INNER JOIN WRY_BASE_INFO b ON a.id=b.wry_key_id INNER JOIN WRY_ENV_BASE_INFO c " +
            "ON a.id=c.wry_key_id LEFT JOIN REGION_CODE r ON b.P_DISTRICT=r.region_code WHERE (b.isbsts='' OR b.isbsts IS NULL) AND a.id=?1 " +
            "AND len (b.P_DISTRICT)=6 AND len (b.oper_ADDRESS)> 1 AND len (a.CREDIT_CD)=18) t WHERE REGIONNAME IS NOT NULL AND " +
            "REGIONNAME !=''",nativeQuery = true)
    List<Map<String, Object>> findByIdNoPush(Long id);

}
